﻿alter table wf_fieldinstance add column sortorder int not null default 0;

ALTER TABLE wf_fieldinstance
  DROP CONSTRAINT wf_fieldinstance_controlid_fkey;

ALTER TABLE wf_fieldinstance
  DROP CONSTRAINT wf_fieldinstance_formid_fkey;

create or replace function create_fieldinstance(_formid int, _start int)
returns void
as
$$
declare
   _i int;
   _row record;
   _sql text;
   _nullable boolean;
begin
   _i := 1;
   _sql = 'create table workflow.form_' || _start::text || '(id serial primary key';
   for _row in select * from wf_field where formid = $1 loop
      _nullable := _row.nullable;
	  if(_row.controlid = 11 and _row.nullable = false) then
	     _nullable = true;
	  end if;

      insert into 
	     wf_fieldinstance(id, templatefieldid, formid, controlid, name, nullable, editable, source, state, argument, defaultvalue, sortorder)
      values(_start + _i, 1, _start, _row.controlid, _row.name, _nullable, _row.editable, _row.source, _row.state, _row.argument, _row.defaultvalue, _i);
      
      _sql = _sql || ',field_' || (_start + _i)::text;
      case _row.controlid
         when 1, 2, 7, 8, 11 then
            _sql = _sql || ' text';
         when 3,9 then
            _sql = _sql || ' timestamp';
         when 4,5,7,10 then
            _sql = _sql || ' int';
         when 6 then
            _sql = _sql || ' numeric(18,4)';
      end case;
      if(_row.controlid <> 11 and _row.nullable = false) then
         _sql = _sql || ' not null';
      end if;

      _i := _i + 1;
   end loop;

   _sql = _sql || ');';

   execute _sql;
end;
$$
language plpgsql;

insert into sys_User(id, loginname, password, salt) values
(27	, 'guanyunke', md5('guanyunke'||'guanyunke'||'xinkai'), 'xinkai');
SELECT pg_catalog.setval('sys_user_id_seq', 27, true);

insert into sys_UserInfo(userid, username, sex, workemail, mobile, idcard, tel) values
(27,  '关运科', '男', 'gyk@xinkaidigital.com', '0','0','0');

insert into sys_Position(id, name) values
(22, '董事长');
SELECT pg_catalog.setval('sys_position_id_seq', 22, true);

insert into sys_UserOrgPosition
values(27, 22, 1, null);

insert into sys_org(name) values('财务部');

update sys_userorgposition set orgid = 6 where userid in (4,5);

INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (1000, 5, '请假表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (2000, 4, '特殊考勤表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (3000, 7, '加班审批表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (4000, 6, '设备领用表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (5000, 3, '杂费报销表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (6000, 8, '付款申请表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (7000, 2, '用章审批表', 1, 1);
INSERT INTO wf_forminstance (id, templateformid, name, state, version) VALUES (8000, 1, '请示审批表', 1, 1);


select create_fieldinstance(5,1000);
select create_fieldinstance(4,2000);
select create_fieldinstance(7,3000);
select create_fieldinstance(6,4000);
select create_fieldinstance(3,5000);
select create_fieldinstance(8,6000);
select create_fieldinstance(2,7000);
select create_fieldinstance(1,8000);



-----------------------------------------------------------------------------------

--insert into wf_dictionary(value, subgroup) values('生产相关', '请示类别');
--insert into wf_dictionary(value, subgroup) values('销售相关', '请示类别');

insert into wf_workflow(id, forminstanceid, name, description, state)
values
(1, 1000, '请假单',    '备注:<br/>具体年假执行标准参照《公司请休假管理规定》相应条款', 2),
(2, 2000, '特殊考勤单', null, 2),
(3, 3000, '加班审批单', null, 2),
(4, 4000, '设备领用单', null, 2),
(5, 5000, '杂费报销单', null, 2),
(6, 6000, '付款申请单', null, 2),
(7, 7000, '用章审批单', null, 2),
(8, 8000, '请示审批单', null, 2),
(9,  7000, '用章审批单(生产相关)', null, 2),
(10, 7000, '用章审批单(销售相关)', null, 2),
(11, 8000, '请示审批单(业务相关)', null, 2);

insert into wf_flowrole
select id, 4 from wf_workflow where id not in (9,10,11)
union 
select id, 5 from wf_workflow where id not in (3,9,10,11)
union
select id, 6 from wf_workflow where id not in (3,9,10,11);


insert into wf_flowrole
values
(9, 7),
(10, 7),
(11, 7);

insert into wf_task(id, worktype, actiontype, touser, todept, toposition, torole, touserlist, state, remark)
values
-- id, worktype, actiontype, touser, todept, toposition, torole, touserlist, state
(   1, 	      1,          4,   null,   null,       null,   null,       null,     1, '直属上级审批'), -- 
(   2,        1,          3,   null,   null,       null,      6,       null,     1, '主管副总审批'),  -- 主管副总审批
(   3,        1,          1,      8,   null,       null,   null,       null,     1, '综合管理部 负责人 曹莹审批'), -- HR审批
(   4,        1,          1,      2,   null,       null,   null,       null,     1, '总经理王珏审批'), -- 王珏审批
(   5,        1,          1,      4,   null,       null,   null,       null,     1, '财务董琳琳审批'), -- 董琳琳审批
(   6,        1,          1,     11,   null,       null,   null,       null,     1, '设备管理王海洋审批'), -- 王海洋审批
(   7,        1,          1,      3,   null,       null,   null,       null,     1, '副董事长审批'), -- 副董事长审批
(   8,        1,          1,     27,   null,       null,   null,       null,     1, '董事长审批'), -- 董事长审批
(   9,        1,          1,     26,   null,       null,   null,       null,     1, '智能交通部 负责人 杨胜军审批'), -- 杨胜军审批
(  10,        1,          1,      6,   null,       null,   null,       null,     1, '谭慧敏审批'); -- 潭慧敏审批

insert into wf_task(id, worktype, actiontype, touser, todept, toposition, torole, touserlist, state,remark)
values
(101, 2, 2, null, null, null, null, '{8,12}'::int[],   1, '抄送 曹莹， 洛晓清'), -- 抄送 曹莹， 洛晓清;
(102, 2, 2, null, null, null, null, '{8,9}'::int[], 1,'抄送 曹莹， 刘晶 '),   -- 抄送 曹莹， 刘晶 
(103, 2, 2, null, null, null, null, '{3,27}'::int[], 1,'抄送 董事长，副董事长'), -- 抄送 董事长，副董事长
(104, 2, 2, null, null, null, null, '{8}'::int[], 1,'抄送 曹莹');	   -- 抄送 曹莹

---- 请假单审批流程
---- 1 执行task  2 条件判断
--INSERT INTO wf_path(
--            id, flowid, typeid, name, condition, taskid, 
--			true_next, false_next)
--values
--(1, 1, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);',       1),
--(2, 1, 1, 3, 'HR审批',       'select check_userrole(@createUserId,4);',       1),
--(3, 1, 3, 101, null,           'select check_userrole(@createUserId,4);',       1),

--(20, 1, 0, 2, '主管副总审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(21, 1, 2, 3, 'HR审批',       'select check_userrole(@createUserId,array[5,6]);', 1),
--(22, 1, 3, 4, '总经理审批',   'select check_userrole(@createUserId,array[5,6]);', 1),
--(23, 1, 4, 101, null,         'select check_userrole(@createUserId,array[5,6]);', 1);

---- 请假单审批流程
---- 部门经理审批
---- HR审批
---- 抄送 刘晶 ， 洛晓清



---- 特殊考勤
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(100, 2, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);',       1),
--(101, 2, 1, 3, 'HR审批',       'select check_userrole(@createUserId,4);',       1),
--(102, 2, 3, 101, null,         'select check_userrole(@createUserId,4);',       1),

--(120, 2, 0, 3, 'HR审批',       'select check_userrole(@createUserId,array[5,6]);', 1),
--(121, 2, 3, 101, null,         'select check_userrole(@createUserId,array[5,6]);', 1);



---- "加班审批单"
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(200, 3, 0, 1, '部门经理审批', null,       1),
--(201, 3, 1, 3, 'HR审批',       null,       1);





---- "设备领用单"
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(300, 4, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);', 1),
--(301, 4, 1, 3, '设备保管人审批', 'select check_userrole(@createUserId,4);', 1),

--(320, 4, 0, 3, '设备保管人审批', 'select check_userrole(@createUserId,array[5,6]);', 1);


---- 杂费报销单
---- form: 5000
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(400, 5, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);', 1),
--(401, 5, 1, 2, '主管副总审批', 'select check_userrole(@createUserId,4);', 1),
--(402, 5, 2, 5, '财务审批', 'select check_userrole(@createUserId,4);', 1),
--(403, 5, 5, 4, '总经理审批', 'select check_userrole(@createUserId,4);', 1),
--(404, 5, 4, 7, '副董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 5004, '' >= 100000'');', 1),
--(405, 5, 7, 8, '董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 5004, '' >= 100000'');', 1),

--(420, 5, 0, 2, '主管副总审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(421, 5, 2, 5, '财务审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(422, 5, 5, 4, '总经理审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(423, 5, 4, 7, '副董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 5004, '' >= 100000'');', 1),
--(424, 5, 7, 8, '董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 5004, '' >= 100000'');', 1);




---- TODO: 付款申请单
---- 高聪特殊处理
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(500, 6, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);', 1),
--(501, 6, 1, 2, '主管副总审批', 'select check_userrole(@createUserId,4);', 1),
--(502, 6, 2, 5, '财务审批', 'select check_userrole(@createUserId,4);', 1),
--(503, 6, 5, 4, '总经理审批', 'select check_userrole(@createUserId,4);', 1),
--(504, 6, 4, 7, '副董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 6006, '' >= 100000'');', 1),
--(505, 6, 7, 8, '董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 6006, '' >= 100000'');', 1),

--(520, 6, 0, 2, '主管副总审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(521, 6, 2, 5, '财务审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(522, 6, 5, 4, '总经理审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(523, 6, 4, 7, '副董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 6006, '' >= 100000'');', 1),
--(524, 6, 7, 8, '董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 6006, '' >= 100000'');', 1);


---- TODO: 用章申请单
---- 高聪特殊处理
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(600, 7, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);', 1),
--(601, 7, 1, 2, '主管副总审批', 'select check_userrole(@createUserId,4);', 1),
--(602, 7, 2, 5, '财务审批', 'select check_userrole(@createUserId,4);', 1),
--(603, 7, 5, 4, '总经理审批', 'select check_userrole(@createUserId,4);', 1),
--(604, 7, 3, 102, null,         'select check_userrole(@createUserId,4);',       1),

--(620, 7, 0, 2, '主管副总审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(621, 7, 2, 5, '财务审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(622, 7, 5, 4, '总经理审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(623, 7, 3, 102, null,  'select check_userrole(@createUserId,array[5,6]);', 1);


---- TODO: 请示审批单
---- 高聪特殊处理
--insert into wf_path(id, flowid, fromtaskid, totaskidsuccess, taskname, sqlcondition, state)
--values
--(700, 8, 0, 1, '部门经理审批', 'select check_userrole(@createUserId,4);', 1),
--(701, 8, 1, 2, '主管副总审批', 'select check_userrole(@createUserId,4);', 1),
--(702, 8, 2, 4, '总经理审批', 'select check_userrole(@createUserId,4);', 1),
--(703, 8, 4, 102, null, 'select check_userrole(@createUserId,4);', 1),
--(704, 8, 102, 7, '副董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 8004, '' >= 100000'');', 1),
--(705, 8, 7, 8, '董事长审批', 'select check_userrole(@createUserId,4) and check_form_data_where(@flowObjectId, @dataId, 8004, '' >= 100000'');', 1),

--(720, 8, 0, 2, '主管副总审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(721, 8, 2, 4, '总经理审批', 'select check_userrole(@createUserId,array[5,6]);', 1),
--(722, 8, 4, 7, '副董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 8004, '' >= 100000'');', 1),
--(723, 8, 7, 8, '董事长审批', 'select check_userrole(@createUserId,array[5,6]) and check_form_data_where(@flowObjectId, @dataId, 8004, '' >= 100000'');', 1);
